﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Xml.Linq;
using System.Xml.Xsl;
using CSW.Framework.Common;
using CSW.Framework.Common.Data;
using CSW.Framework.Common.Utils;
using CSW.Framework.Common.Utils.StringUtils;
using CSW.Framework.Linq.SchemaModel;
using LinqBusinessObjectBuilder.Options;
using LinqBusinessObjectBuilder.ProjectInfo;
using UniqueConstraint = CSW.Framework.Linq.SchemaModel.UniqueConstraint;
using CSW.Framework.Common.Utils.Hash;

namespace LinqBusinessObjectBuilder.Generator
{
    internal class CodeGenerator
    {
        public static Database GetUpdatedSchema(Project project)
        {
            Guard.ArgumentNotNull(project, "project");

            Database db;
            if (project.DataProvider.Name == DataProvider.SqlServer.Name)
            {
                db = GetUpdatedSchema_SqlServer(project);
            }
            else if (project.DataProvider.Name == DataProvider.SqlServerCe.Name)
            {
                db = GetUpdatedSchema_SqlServerCe(project);
            }
            else
            {
                throw new Exception("Unknown Data Provider");
            }

            return db;
        }

        private static Database GetUpdatedSchema_SqlServerCe(Project project)
        {
            Guard.ArgumentNotNull(project, "project");

            List<Table> tableList = new List<Table>();
            List<DataTypeMapping> dataTypeMappingList = new List<DataTypeMapping>();

            using (SqlCeConnection conn = new SqlCeConnection(string.Format("data source='{0}'; mode=Read Write;", project.Server)))
            {
                conn.Open();

                DataTable dt = SqlCeDataHelper.GetDataTable(conn, "select * from information_schema.tables");

                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["TABLE_TYPE"].Equals("TABLE"))
                    {
                        Table table = new Table();
                        table.Schema = DataConverter.GetString(dr["TABLE_SCHEMA"]);
                        table.TableName = DataConverter.GetString(dr["TABLE_NAME"]);
                        table.FullClassName = string.Format("{0}.{1}", project.Namespace, table.ClassName);

                        tableList.Add(table);
                    }
                }

                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Int16", DbType = "smallint" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Int32", DbType = "int" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Single", DbType = "real" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Double", DbType = "float" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Decimal", DbType = "money" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Boolean", DbType = "bit" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.SByte", DbType = "tinyint" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Int64", DbType = "bigint" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Guid", DbType = "uniqueidentifier" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Byte[]", DbType = "varbinary" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Byte[]", DbType = "binary" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Byte[]", DbType = "image" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.String", DbType = "nvarchar" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.String", DbType = "nchar" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.String", DbType = "ntext" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Decimal", DbType = "numeric" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.DateTime", DbType = "datetime" });
                dataTypeMappingList.Add(new DataTypeMapping { DataType = "System.Byte[]", DbType = "rowversion" });

                /*dt = conn.GetSchema("DataTypes");
                foreach (DataRow dr in dt.Rows)
                {
                    DataTypeMapping dataTypeMapping = new DataTypeMapping();
                    dataTypeMapping.DbType = dr["TypeName"].ToString();
                    dataTypeMapping.DataType = dr["DataType"].ToString();
                    dataTypeMappingList.Add(dataTypeMapping);
                }*/

                dt = SqlCeDataHelper.GetDataTable(conn, "select * from information_schema.columns");
                foreach (DataRow dr in dt.Rows)
                {
                    string tableSchema = DataConverter.GetString(dr["TABLE_SCHEMA"]);
                    string tableName = DataConverter.GetString(dr["TABLE_NAME"]);

                    Table table = GetTable(tableList, tableSchema, tableName);
                    if (table == null)
                        continue;

                    string dbDataType = dr["DATA_TYPE"].ToString();

                    int characterMaxLength;
                    int.TryParse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString(), out characterMaxLength);

                    TableColumn tableColumn = new TableColumn();
                    tableColumn.ColumnName = dr["COLUMN_NAME"].ToString();
                    tableColumn.IsNullable = dr["IS_NULLABLE"].Equals("YES");
                    tableColumn.DbDataType = dbDataType;
                    tableColumn.CharacterMaxLength = characterMaxLength > 0 ? (int?)characterMaxLength : null;
                    tableColumn.DefaultValue = dr["COLUMN_DEFAULT"].ToString();

                    table.Columns.Add(tableColumn);

                    TableProperty property = new TableProperty();
                    property.PropertyName = table.GetUniquePropertyName(tableColumn.ColumnName);
                    property.Column = tableColumn;
                    property.DataType = dataTypeMappingList.Where(p => p.DbType == dbDataType).Single().DataType;
                    property.IsChildContainer = false;
                    property.IsChildList = false;
                    property.IsPrimitiveType = true;
                    table.Properties.Add(property);

                    if (property.Type != typeof(string) && tableColumn.CharacterMaxLength != null)
                    {
                        tableColumn.CharacterMaxLength = null;
                    }
                }

                foreach (Table table in tableList)
                {
                    string selectStatement = string.Format("select * from [{0}]", table.TableName);

                    // Get primary key
                    using (SqlCeCommand cmd = new SqlCeCommand(selectStatement, conn))
                    using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                    {
                        dt = new DataTable();
                        da.FillSchema(dt, SchemaType.Source);
                        foreach (DataColumn dataColumn in dt.PrimaryKey)
                        {
                            foreach (TableColumn tableColumn in table.Columns)
                            {
                                if (dataColumn.ColumnName == tableColumn.ColumnName)
                                    tableColumn.IsKey = true;
                            }
                        }
                    }

                    // Additional column properties
                    using (SqlCeCommand cmd = new SqlCeCommand(selectStatement, conn))
                    {
                        SqlCeDataReader reader = cmd.ExecuteReader();
                        if (reader != null)
                        {
                            try
                            {
                                dt = reader.GetSchemaTable();
                                foreach (DataRow dr in dt.Rows)
                                {
                                    foreach (TableColumn tableColumn in table.Columns)
                                    {
                                        if (dr["ColumnName"].ToString() == tableColumn.ColumnName)
                                        {
                                            tableColumn.IsIdentity = (bool)dr["IsIdentity"];
                                            tableColumn.IsRowVersion = (bool)dr["IsRowVersion"];
                                            tableColumn.IsReadOnly = (bool)dr["IsReadOnly"];
                                            tableColumn.IsNullable = (bool)dr["AllowDBNull"];
                                        }
                                    }
                                }
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                }

                // Remove tables without primary keys
                foreach (Table table in new List<Table>(tableList))
                {
                    if (table.PrimaryKey.Length == 0)
                    {
                        Console.WriteLine("Removing " + table);
                        tableList.Remove(table);
                    }
                }

                // Foreign keys
                using (SqlCeCommand cmd = new SqlCeCommand(m_GetForeignKeysStatementSqlCe, conn))
                using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        Table table = GetTable(tableList, dr["Schema"].ToString(), dr["TableName"].ToString());
                        if (table == null) // table not found, probably removed due to no PK
                            continue;

                        // ReSharper disable AccessToModifiedClosure
                        TableColumn column = table.Columns.Where(p => p.ColumnName == dr["ColumnName"].ToString()).Single();
                        // ReSharper restore AccessToModifiedClosure
                        column.FKID = CRC32.CalculateInt32(Encoding.ASCII.GetBytes(dr["CONSTRAINT_NAME"].ToString()));
                        column.FKTableSchema = dr["RefSchema"].ToString();
                        column.FKTableName = dr["RefTableName"].ToString();

                        Table fkTable = GetTable(tableList, column.FKTableSchema, column.FKTableName);
                        column.FKTableColumnName = fkTable.PrimaryKey[0].ColumnName;

                        if (column.FKID != null)
                        {
                            string propertyName = column.ColumnName;

                            if (propertyName.EndsWith("ID") || propertyName.EndsWith("Id"))
                                propertyName = propertyName.Substring(0, propertyName.Length - 2);

                            TableProperty property = new TableProperty();
                            property.PropertyName = table.GetUniquePropertyName(propertyName);
                            property.Column = column;
                            // TODO: set datatype?
                            property.IsChildContainer = false;
                            property.IsChildList = false;
                            property.IsPrimitiveType = false;
                            table.Properties.Add(property);
                        }
                    }
                }

                // Unique constraints
                using (SqlCeCommand cmd = new SqlCeCommand(m_GetUniqueConstraintsStatementSqlCe, conn))
                using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        Table table = GetTable(tableList, dr["Schema"].ToString(), dr["TableName"].ToString());
                        if (table == null) // table not found, probably removed due to no PK
                            continue;

                        // ReSharper disable AccessToModifiedClosure
                        TableColumn column = table.Columns.Where(p => p.ColumnName == dr["ColumnName"].ToString()).Single();
                        // ReSharper restore AccessToModifiedClosure

                        string constraintName = dr["ConstraintName"].ToString();
                        UniqueConstraint uq = table.UniqueConstraints.Where(p => p.ConstraintName == constraintName).SingleOrDefault();
                        if (uq == null)
                        {
                            uq = new UniqueConstraint { ConstraintName = constraintName };
                            table.UniqueConstraints.Add(uq);
                        }
                        uq.Columns.Add(column);
                    }
                }

                // Computed columns
                // TODO
                /*using (SqlCeCommand cmd = new SqlCeCommand(m_GetSysColumnsStatement, conn))
                using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        Table table = GetTable(tableList, dr["Schema"].ToString(), dr["TableName"].ToString());
                        if (table == null) // table not found, probably removed due to no PK
                            continue;
                  
                        // ReSharper disable AccessToModifiedClosure
                        TableColumn column = table.Columns.Where(p => p.ColumnName == dr["ColumnName"].ToString()).Single();
                        // ReSharper restore AccessToModifiedClosure

                        bool isComputed = DataConverter.GetBoolean(dr["IsComputed"]);
                        column.IsComputed = isComputed;
                    }
                }*/

            }

            // Child items
            foreach (Table table in tableList)
            {
                foreach (Table refTable in tableList)
                {
                    // ReSharper disable AccessToModifiedClosure
                    List<TableProperty> properties = table.Properties.Where(p => !p.IsPrimitiveType && (p.Column != null && (p.Column.FKTableSchema == refTable.Schema && p.Column.FKTableName == refTable.TableName))).ToList();
                    // ReSharper restore AccessToModifiedClosure

                    foreach (TableProperty property in properties)
                    {
                        // If there is more than one foreign key pointing to the same table,
                        // or if the table references itself
                        string propertyName;
                        string originalPropertyName = property.PropertyName;
                        if (properties.Count > 1 || refTable == table)
                            propertyName = string.Format("{0}_{1}", refTable.ClassName, property.PropertyName);
                        else
                            propertyName = refTable.ClassName;

                        property.SetPropertyName(table.GetUniquePropertyName(property, propertyName));
                        property.IsPrimitiveType = false;
                        property.IsChildContainer = false;
                        property.IsChildList = false;
                        property.DataType = refTable.ClassName;

                        // For PK's and UNIQUE FK's use a single object
                        if (refTable != table)
                        {
                            bool isUnique = table.UniqueConstraints.Where(p => p.Columns.Count == 1 && p.Columns.Contains(property.Column)).Any();
                            if (isUnique || table.PrimaryKey.Contains(property.Column))
                            {
                                refTable.Properties.Add(new TableProperty
                                {
                                    Column = null,
                                    IsChildContainer = true,
                                    IsChildList = false,
                                    IsPrimitiveType = false,
                                    DataType = table.ClassName,
                                    ChildDataType = table.FullClassName,
                                    PropertyName = refTable.GetUniquePropertyName(table.ClassName),
                                    ChildColumnName = property.Column.ColumnName
                                });
                            }
                            else
                            {
                                // BindingList on reverse

                                string blPropertyName = table.ClassName;
                                if (properties.Count > 1)
                                {
                                    blPropertyName = string.Format("{0}_{1}", table.ClassName, originalPropertyName);
                                }

                                refTable.Properties.Add(new TableProperty
                                {
                                    Column = null,
                                    IsChildContainer = true,
                                    IsChildList = true,
                                    IsPrimitiveType = false,
                                    DataType = string.Format("BindingList<{0}>", table.ClassName),
                                    ChildDataType = table.FullClassName,
                                    PropertyName = refTable.GetUniquePropertyName(blPropertyName),
                                    ChildColumnName = property.Column.ColumnName
                                });
                            }
                        }
                    }
                }
            }

            Database db = new Database();
            db.DatabaseName = project.Database;
            db.Provider = project.DataProvider.Name;
            db.Tables.AddRange(tableList);
            return db;
        }

        private static Database GetUpdatedSchema_SqlServer(Project project)
        {
            Guard.ArgumentNotNull(project, "project");

            SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
            connStr.DataSource = project.Server;
            connStr.InitialCatalog = project.Database;
            if (project.AuthenticationMode == AuthenticationMode.WindowsAuthentication)
            {
                connStr.IntegratedSecurity = true;
            }
            else
            {
                connStr.UserID = project.User;
                connStr.Password = project.Password;
            }

            List<Table> tableList = new List<Table>();
            List<DataTypeMapping> dataTypeMappingList = new List<DataTypeMapping>();

            using (SqlConnection conn = new SqlConnection(connStr.ToString()))
            {
                conn.Open();

                DataTable dt = conn.GetSchema("Tables");
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["TABLE_TYPE"].Equals("BASE TABLE"))
                    {
                        Table table = new Table();
                        table.Schema = dr["TABLE_SCHEMA"].ToString();
                        table.TableName = dr["TABLE_NAME"].ToString();
                        table.FullClassName = string.Format("{0}.{1}", project.Namespace, table.ClassName);

                        tableList.Add(table);
                    }
                }

                dt = conn.GetSchema("DataTypes");
                foreach (DataRow dr in dt.Rows)
                {
                    DataTypeMapping dataTypeMapping = new DataTypeMapping();
                    dataTypeMapping.DbType = dr["TypeName"].ToString();
                    dataTypeMapping.DataType = dr["DataType"].ToString();
                    dataTypeMappingList.Add(dataTypeMapping);

                    Console.WriteLine(dataTypeMapping);
                }

                dt = conn.GetSchema("Columns");
                foreach (DataRow dr in dt.Rows)
                {
                    string tableSchema = dr["TABLE_SCHEMA"].ToString();
                    string tableName = dr["TABLE_NAME"].ToString();

                    Table table = GetTable(tableList, tableSchema, tableName);
                    if (table == null)
                        continue;

                    string dbDataType = dr["DATA_TYPE"].ToString();

                    int characterMaxLength;
                    int.TryParse(dr["CHARACTER_MAXIMUM_LENGTH"].ToString(), out characterMaxLength);

                    TableColumn tableColumn = new TableColumn();
                    tableColumn.ColumnName = dr["COLUMN_NAME"].ToString();
                    //tableColumn.IsNullable = dr["IS_NULLABLE"].Equals("YES");
                    tableColumn.DbDataType = dbDataType;
                    tableColumn.CharacterMaxLength = characterMaxLength > 0 ? (int?)characterMaxLength : null;
                    tableColumn.DefaultValue = dr["COLUMN_DEFAULT"].ToString();

                    table.Columns.Add(tableColumn);

                    TableProperty property = new TableProperty();
                    property.PropertyName = table.GetUniquePropertyName(tableColumn.ColumnName);
                    property.Column = tableColumn;
                    property.DataType = dataTypeMappingList.Where(p => p.DbType == dbDataType).Single().DataType;
                    property.IsChildContainer = false;
                    property.IsChildList = false;
                    property.IsPrimitiveType = true;
                    table.Properties.Add(property);
                }

                foreach (Table table in tableList)
                {
                    string selectStatement = string.Format("select top 0 * from [{0}].[{1}]", table.Schema, table.TableName);

                    // Get primary key
                    using (SqlCommand cmd = new SqlCommand(selectStatement, conn))
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        dt = new DataTable();
                        da.FillSchema(dt, SchemaType.Source);
                        foreach (DataColumn dataColumn in dt.PrimaryKey)
                        {
                            foreach (TableColumn tableColumn in table.Columns)
                            {
                                if (dataColumn.ColumnName == tableColumn.ColumnName)
                                    tableColumn.IsKey = true;
                            }
                        }

                        if (dt.PrimaryKey.Length > 1)
                        {
                        }
                    }

                    // Additional column properties
                    using (SqlCommand cmd = new SqlCommand(selectStatement, conn))
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader != null)
                        {
                            try
                            {
                                dt = reader.GetSchemaTable();
                                foreach (DataRow dr in dt.Rows)
                                {
                                    foreach (TableColumn tableColumn in table.Columns)
                                    {
                                        if (dr["ColumnName"].ToString() == tableColumn.ColumnName)
                                        {
                                            tableColumn.IsIdentity = (bool)dr["IsIdentity"];
                                            tableColumn.IsRowVersion = (bool)dr["IsRowVersion"];
                                            tableColumn.IsReadOnly = (bool)dr["IsReadOnly"];
                                            tableColumn.IsNullable = (bool)dr["AllowDBNull"];
                                        }
                                    }
                                }
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                }

                // Remove tables without primary keys
                foreach (Table table in new List<Table>(tableList))
                {
                    if (table.PrimaryKey.Length == 0)
                    {
                        Console.WriteLine("Removing " + table);
                        tableList.Remove(table);
                    }
                }

                // Foreign keys
                using (SqlCommand cmd = new SqlCommand(m_GetForeignKeysStatement, conn))
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        Table table = GetTable(tableList, dr["Schema"].ToString(), dr["TableName"].ToString());
                        if (table == null) // table not found, probably removed due to no PK
                            continue;

                        // ReSharper disable AccessToModifiedClosure
                        TableColumn column = table.Columns.Where(p => p.ColumnName == dr["ColumnName"].ToString()).Single();
                        // ReSharper restore AccessToModifiedClosure
                        column.FKID = (int)dr["constid"];
                        column.FKTableSchema = dr["RefSchema"].ToString();
                        column.FKTableName = dr["RefTableName"].ToString();
                        column.FKTableColumnName = dr["RefColumnName"].ToString();

                        if (column.FKID != null)
                        {
                            string propertyName = column.ColumnName;

                            if (propertyName.EndsWith("ID") || propertyName.EndsWith("Id"))
                                propertyName = propertyName.Substring(0, propertyName.Length - 2);

                            TableProperty property = new TableProperty();
                            property.PropertyName = table.GetUniquePropertyName(propertyName);
                            property.Column = column;
                            // TODO: set datatype?
                            property.IsChildContainer = false;
                            property.IsChildList = false;
                            property.IsPrimitiveType = false;
                            table.Properties.Add(property);
                        }
                    }
                }

                // Unique constraints
                using (SqlCommand cmd = new SqlCommand(m_GetUniqueConstraintsStatement, conn))
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        Table table = GetTable(tableList, dr["Schema"].ToString(), dr["TableName"].ToString());
                        if (table == null) // table not found, probably removed due to no PK
                            continue;

                        // ReSharper disable AccessToModifiedClosure
                        TableColumn column = table.Columns.Where(p => p.ColumnName == dr["ColumnName"].ToString()).Single();
                        // ReSharper restore AccessToModifiedClosure

                        string constraintName = dr["ConstraintName"].ToString();
                        UniqueConstraint uq = table.UniqueConstraints.Where(p => p.ConstraintName == constraintName).SingleOrDefault();
                        if (uq == null)
                        {
                            uq = new UniqueConstraint { ConstraintName = constraintName };
                            table.UniqueConstraints.Add(uq);
                        }
                        uq.Columns.Add(column);
                    }
                }

                // Computed columns
                using (SqlCommand cmd = new SqlCommand(m_GetSysColumnsStatement, conn))
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);

                    foreach (DataRow dr in dt.Rows)
                    {
                        Table table = GetTable(tableList, dr["Schema"].ToString(), dr["TableName"].ToString());
                        if (table == null) // table not found, probably removed due to no PK
                            continue;

                        // ReSharper disable AccessToModifiedClosure
                        TableColumn column = table.Columns.Where(p => p.ColumnName == dr["ColumnName"].ToString()).Single();
                        // ReSharper restore AccessToModifiedClosure

                        bool isComputed = DataConverter.GetBoolean(dr["IsComputed"]);
                        column.IsComputed = isComputed;
                    }
                }

            }

            // Child items
            foreach (Table table in tableList)
            {
                foreach (Table refTable in tableList)
                {
                    // ReSharper disable AccessToModifiedClosure
                    List<TableProperty> properties = table.Properties.Where(p => !p.IsPrimitiveType && (p.Column != null && (p.Column.FKTableSchema == refTable.Schema && p.Column.FKTableName == refTable.TableName))).ToList();
                    // ReSharper restore AccessToModifiedClosure

                    foreach (TableProperty property in properties)
                    {
                        // If there is more than one foreign key pointing to the same table,
                        // or if the table references itself
                        string propertyName;
                        string originalPropertyName = property.PropertyName;
                        if (properties.Count > 1 || refTable == table)
                            propertyName = string.Format("{0}_{1}", refTable.ClassName, property.PropertyName);
                        else
                            propertyName = refTable.ClassName;

                        property.SetPropertyName(table.GetUniquePropertyName(property, propertyName));
                        property.IsPrimitiveType = false;
                        property.IsChildContainer = false;
                        property.IsChildList = false;
                        property.DataType = refTable.ClassName;

                        // For PK's and UNIQUE FK's use a single object
                        if (refTable != table)
                        {
                            bool isUnique = table.UniqueConstraints.Where(p => p.Columns.Count == 1 && p.Columns.Contains(property.Column)).Any();
                            if (isUnique || table.PrimaryKey.Contains(property.Column))
                            {
                                refTable.Properties.Add(new TableProperty
                                {
                                    Column = null,
                                    IsChildContainer = true,
                                    IsChildList = false,
                                    IsPrimitiveType = false,
                                    DataType = table.ClassName,
                                    ChildDataType = table.FullClassName,
                                    PropertyName = refTable.GetUniquePropertyName(table.ClassName),
                                    ChildColumnName = property.Column.ColumnName
                                });
                            }
                            else
                            {
                                // BindingList on reverse

                                string blPropertyName = table.ClassName;
                                if (properties.Count > 1)
                                {
                                    blPropertyName = string.Format("{0}_{1}", table.ClassName, originalPropertyName);
                                }

                                refTable.Properties.Add(new TableProperty
                                {
                                    Column = null,
                                    IsChildContainer = true,
                                    IsChildList = true,
                                    IsPrimitiveType = false,
                                    DataType = string.Format("BindingList<{0}>", table.ClassName),
                                    ChildDataType = table.FullClassName,
                                    PropertyName = refTable.GetUniquePropertyName(blPropertyName),
                                    ChildColumnName = property.Column.ColumnName
                                });
                            }
                        }
                    }
                }
            }

            Database db = new Database();
            db.DatabaseName = project.Database;
            db.Provider = project.DataProvider.Name;
            db.Tables.AddRange(tableList);
            return db;
        }

        private static Table GetTable(IEnumerable<Table> tableList, string tableSchema, string tableName)
        {
            return tableList.Where(p => p.Schema == tableSchema && p.TableName == tableName).SingleOrDefault();
        }

        /// <summary>
        /// Generates classes, project files, and the solution file.
        /// </summary>
        /// <param name="project">The project.</param>
        /// <param name="options">The <see cref="GlobalOptions"/> object.</param>
        /// <returns>The path of the solution file.</returns>
        public static string Generate(Project project, GlobalOptions options)
        {
            Guard.ArgumentNotNull(project, "project");
            Guard.ArgumentNotNullOrEmptyString(project.OutputPath, "project.OutputPath");
            Guard.ArgumentNotNullOrEmptyString(project.SolutionName, "project.SolutionName");
            Guard.ArgumentNotNull(options, "options");

            string solutionOutputDirectory = project.OutputPath;
            string projectOutputDirectory = Path.Combine(solutionOutputDirectory, project.Namespace);
            string solutionName = project.SolutionName;

            if (!File.Exists(options.CSWCommonPath))
                throw new FileNotFoundException(string.Format("'{0}' not found.", options.CSWCommonPath), options.CSWCommonPath);
            if (!File.Exists(options.CSWLinqPath))
                throw new FileNotFoundException(string.Format("'{0}' not found.", options.CSWLinqPath), options.CSWLinqPath);
            if (!File.Exists(options.CSWUIPath))
                throw new FileNotFoundException(string.Format("'{0}' not found.", options.CSWUIPath), options.CSWUIPath);
            if (!File.Exists(options.CSWUIDevExPath))
                throw new FileNotFoundException(string.Format("'{0}' not found.", options.CSWUIDevExPath), options.CSWUIDevExPath);

            Database db = GetUpdatedSchema(project);

            List<string> projectFiles = CreateClasses(projectOutputDirectory, project.Namespace, db);

            string appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

            string projectFileInputPath = Path.Combine(appPath, @"Templates\Business\Business.csproj.xslt");
            string projectFileOutputPath = Path.Combine(projectOutputDirectory, project.Namespace + ".csproj");
            string solutionFileInputPath = Path.Combine(appPath, @"Templates\Solution.sln.xslt");
            string solutionFileOutputPath = Path.Combine(solutionOutputDirectory, solutionName);
            if (Path.GetExtension(solutionFileOutputPath).ToLower() != ".sln")
                solutionFileOutputPath += ".sln";

            string projectGuid = Guid.NewGuid().ToString().ToUpper();
            string projectAssemblyName = project.Namespace;

            // fileNameStartIndex is used to get relative paths from the project root
            int fileNameStartIndex = projectOutputDirectory.Length + (projectOutputDirectory.EndsWith(@"\") ? 0 : 1);
            //string shortOutputFileName = outputFileName.Substring(fileNameStartIndex);

            XElement xml = new XElement("project",
                             new[] {
                               new XElement("guid", projectGuid),
                               new XElement("root_namespace", project.Namespace),
                               new XElement("assembly_name", projectAssemblyName),
                               //new XElement("name", shortOutputFileName)
                             }
                           );

            // XML Schema File
            string schemaXmlFileName = Path.Combine(projectOutputDirectory, project.Database + "Db.xml");
            db.CreateXml().Save(schemaXmlFileName);
            Database.LoadXml(XDocument.Load(schemaXmlFileName));

            xml.Add(new XElement("content", new XElement("filename", schemaXmlFileName.Substring(fileNameStartIndex))));

            // Source Files
            xml.Add(projectFiles.Select(p => new XElement("file", new XElement("filename", p.Substring(fileNameStartIndex)))));

            // References
            xml.Add(new XElement("project_reference", new[] { 
                                                        new XElement("path", options.CSWCommonPath),
                                                        new XElement("guid", "B946DC07-1745-49F4-9614-EA803E4763A5"),
                                                        new XElement("name", "CSW.Framework.Common")
                                                      }));

            xml.Add(new XElement("project_reference", new[] { 
                                                        new XElement("path", options.CSWLinqPath),
                                                        new XElement("guid", "B43719D5-BC44-4B55-9AFB-186EB828C319"),
                                                        new XElement("name", "CSW.Framework.Linq")
                                                      }));

            // .Business.csproj
            if (!File.Exists(projectFileOutputPath))
            {
                XslCompiledTransform xslTran = new XslCompiledTransform();
                xslTran.Load(projectFileInputPath);
                using (StreamWriter writer = new StreamWriter(File.Open(projectFileOutputPath, FileMode.Create, FileAccess.Write, FileShare.None)))
                {
                    xslTran.Transform((new XDocument(xml)).CreateReader(), null, writer);
                }
            }
            else
            {
                XDocument xBusProj = XDocument.Load(projectFileOutputPath);
                XElement projectElement = xBusProj.Root;
                if (projectElement == null)
                    throw new InvalidDataException(string.Format("{0} does not contain a root element", projectFileOutputPath));
                IEnumerable<XElement> itemGroups = projectElement.Elements().Where(p => p.Name.LocalName == "ItemGroup");

                // Compile ItemGroup

                XElement compileItemGroup = itemGroups.Where(p => p.Elements().Where(q => q.Name.LocalName == "Compile").Any()).SingleOrDefault();
                if (compileItemGroup == null)
                    throw new InvalidDataException(string.Format("{0} does not contain a single ItemGroup containing Compile elements", projectFileOutputPath));

                IEnumerable<XElement> fileElements = xml.Elements("file");
                foreach (XElement compileItem in compileItemGroup.Elements().Where(p => p.Name.LocalName == "Compile"))
                {
                    string value = compileItem.Attributes().Where(p => p.Name.LocalName == "Include").Single().Value;

                    XElement element = fileElements.Where(p => p.Element("filename").Value == value).SingleOrDefault();
                    if (element != null)
                        element.Remove();
                }

                foreach (XElement element in fileElements)
                {
                    compileItemGroup.Add(
                        new XElement(
                            "{http://schemas.microsoft.com/developer/msbuild/2003}Compile",
                            new XAttribute(
                                "Include",
                                element.Element("filename").Value
                            )
                        )
                    );
                }

                // Content item group
                XElement contentItemGroup = itemGroups.Where(p => p.Elements().Where(q => q.Name.LocalName == "Content").Any()).SingleOrDefault();
                if (contentItemGroup == null)
                {
                    contentItemGroup = new XElement("{http://schemas.microsoft.com/developer/msbuild/2003}ItemGroup");
                    projectElement.Add(contentItemGroup);
                }

                IEnumerable<XElement> contentElements = xml.Elements("content");
                foreach (XElement contentItem in contentItemGroup.Elements().Where(p => p.Name.LocalName == "Content"))
                {
                    string value = contentItem.Attributes().Where(p => p.Name.LocalName == "Include").Single().Value;

                    XElement element = contentElements.Where(p => p.Element("filename").Value == value).SingleOrDefault();
                    if (element != null)
                    {
                        element.Remove();
                    }
                }

                foreach (XElement element in contentElements)
                {
                    contentItemGroup.Add(
                        new XElement(
                            "{http://schemas.microsoft.com/developer/msbuild/2003}Content",
                            new XAttribute(
                                "Include",
                                element.Element("filename").Value
                            ),
                            new XElement("{http://schemas.microsoft.com/developer/msbuild/2003}CopyToOutputDirectory", "PreserveNewest")
                        )
                    );
                }


                if (fileElements.Any() || contentElements.Any())
                    xBusProj.Save(projectFileOutputPath);
            }

            // Solution file
            // Do not overwrite if already exists
            if (!File.Exists(solutionFileOutputPath))
            {
                xml = new XElement("solution");

                xml.Add(new XElement("guid", Guid.NewGuid().ToString().ToUpper()));

                xml.Add(new XElement("project", new[] { 
                    new XElement("path", options.CSWCommonPath),
                    new XElement("guid", "B946DC07-1745-49F4-9614-EA803E4763A5"),
                    new XElement("assembly_name", "CSW.Framework.Common")
                }));

                xml.Add(new XElement("project", new[] { 
                    new XElement("path", options.CSWLinqPath),
                    new XElement("guid", "B43719D5-BC44-4B55-9AFB-186EB828C319"),
                    new XElement("assembly_name", "CSW.Framework.Linq")
                }));

                xml.Add(new XElement("project", new[] { 
                    new XElement("path", options.CSWUIPath),
                    new XElement("guid", "C760B2CF-81CA-4750-8F8F-5E974590C8C9"),
                    new XElement("assembly_name", "CSW.Framework.UI")
                }));

                xml.Add(new XElement("project", new[] { 
                    new XElement("path", options.CSWUIDevExPath),
                    new XElement("guid", "A8411613-5E20-4ACD-9080-DE4A7CE7F6B8"),
                    new XElement("assembly_name", "CSW.Framework.UI.DevEx")
                }));

                xml.Add(new XElement("project", new[] { 
                    new XElement("path", PathUtils.GetRelativePath(solutionOutputDirectory, projectFileOutputPath)),
                    new XElement("guid", projectGuid),
                    new XElement("assembly_name", projectAssemblyName)
                }));

                XslCompiledTransform xslTran = new XslCompiledTransform();
                xslTran.Load(solutionFileInputPath);
                using (StreamWriter writer = new StreamWriter(File.Open(solutionFileOutputPath, FileMode.Create, FileAccess.Write, FileShare.None)))
                {
                    xslTran.Transform((new XDocument(xml)).CreateReader(), null, writer);
                }
            }

            return solutionFileOutputPath;
        }

        private static List<string> CreateClasses(string projectOutputDirectory, string baseNamespace, Database db)
        {
            Guard.ArgumentNotNullOrEmptyString(projectOutputDirectory, "projectOutputDirectory");
            Guard.ArgumentNotNullOrEmptyString(baseNamespace, "baseNamespace");
            Guard.ArgumentNotNull(db, "db");

            List<string> projectFiles = new List<string>();

            foreach (Table table in db.Tables)
            {
                foreach (TableColumn column in table.Columns)
                {
                    if (column.FKID != null && column.FKTable == null)
                    {
                        foreach (Table fkTable in db.Tables)
                        {
                            if (column.FKTableName == fkTable.TableName &&
                                column.FKTableSchema == fkTable.Schema)
                            {
                                column.FKTable = fkTable;
                            }
                        }
                    }
                }
            }

            foreach (Table table in db.Tables)
            {
                if (table.PrimaryKey.Length > 1)
                {
                }

                string[] fileName = CreateClass(projectOutputDirectory, baseNamespace, db, table);
                projectFiles.AddRange(fileName);
            }

            projectFiles.AddRange(CreateDbClass(projectOutputDirectory, baseNamespace, db));

            return projectFiles;
        }

        /// <summary>
        /// Creates the db class.
        /// </summary>
        /// <param name="projectOutputDirectory">The project output directory.</param>
        /// <param name="baseNamespace">The base namespace.</param>
        /// <param name="db">The db.</param>
        /// <returns></returns>
        private static IEnumerable<string> CreateDbClass(string projectOutputDirectory, string baseNamespace, Database db)
        {
            Guard.ArgumentNotNullOrEmptyString(projectOutputDirectory, "projectOutputDirectory");
            Guard.ArgumentNotNullOrEmptyString(baseNamespace, "baseNamespace");
            Guard.ArgumentNotNull(db, "db");

            string dbClassName = GetDbClassName(db);
            string dbClassPath = Path.Combine(projectOutputDirectory, dbClassName + ".cs");

            string sqlConnectionClass = db.Provider == "SQL Server" ? "SqlConnection" : "SqlCeConnection";
            string dataHelperClass = db.Provider == "SQL Server" ? "DataHelper" : "SqlCeDataHelper";
            string sqlParameterClass = db.Provider == "SQL Server" ? "SqlParameter" : "SqlCeParameter";

            StringBuilder sb = new StringBuilder();
            AppendAutoGeneratedComment(sb);
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Configuration;");
            sb.AppendLine("using System.Data;");
            sb.AppendLine("using System.Data.Common;");
            sb.AppendLine("using System.Data.SqlClient;");
            if (db.Provider == "SQL Server CE")
                sb.AppendLine("using System.Data.SqlServerCe;");
            sb.AppendLine("using System.IO;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Reflection;");
            sb.AppendLine("using System.Text;");
            sb.AppendLine("using System.Threading;");
            sb.AppendLine("using System.Transactions;");
            sb.AppendLine("using System.Xml.Linq;");
            sb.AppendLine("using CSW.Framework.Common;");
            sb.AppendLine("using CSW.Framework.Common.Data;");
            sb.AppendLine("using CSW.Framework.Linq;");
            sb.AppendLine("using CSW.Framework.Linq.SchemaModel;");
            sb.AppendLine();
            sb.AppendLine(string.Format("namespace {0}", baseNamespace));
            sb.AppendLine("{");
            sb.AppendLine(string.Format("    public class {0} : IDatabase", dbClassName));
            sb.AppendLine("    {");
            sb.AppendLine("        private static readonly Dictionary<string, Type> m_TableTypeDictionary;");
            sb.AppendLine("        private static readonly Database m_DbSchema;");
            sb.AppendLine("        private static readonly string m_ConnectionString;");
            sb.AppendLine("        private static readonly Dictionary<Thread, " + sqlConnectionClass + "> m_ThreadConnection;");
            sb.AppendLine("        private static readonly object m_ThreadConnectionLocker = new object();");

            /*foreach (Table table in db.Tables)
            {
                sb.AppendLine(string.Format("        private static TableQueryProvider<{0}> m_{0};", table.ClassName));
            }*/
            sb.AppendLine();
            sb.AppendLine(string.Format("        static {0}()", dbClassName));
            sb.AppendLine("        {");
            sb.AppendLine(string.Format("            m_DbSchema = Database.LoadXml(XDocument.Load(Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), \"{0}.xml\")));", dbClassName));
            sb.AppendLine();
            sb.AppendLine(string.Format("            ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings[\"{0}\"];", db.ConnectionStringKey));
            sb.AppendLine(string.Format("            if (connSettings == null)"));
            sb.AppendLine(string.Format("                throw new Exception(\"Connection string '{0}' not found in configuration file.\");", db.ConnectionStringKey));
            sb.AppendLine(string.Format("            m_ConnectionString = connSettings.ConnectionString;"));
            sb.AppendLine();
            sb.AppendLine(string.Format("            m_ThreadConnection = new Dictionary<Thread, " + sqlConnectionClass + ">();"));
            sb.AppendLine();
            sb.AppendLine(string.Format("            GetThreadConnectionStatic();"));
            sb.AppendLine();
            sb.AppendLine("            m_TableTypeDictionary = new Dictionary<string, Type>();");
            foreach (Table table in db.Tables)
            {
                sb.AppendLine(string.Format("            m_TableTypeDictionary.Add(\"{0}\", typeof({1}));", table.FullClassName, table.FullClassName.Substring(baseNamespace.Length + 1)));
            }
            sb.AppendLine();
            /*foreach (Table table in db.Tables)
            {
                sb.AppendLine(string.Format("            m_{0} = new TableQueryProvider<{0}>();", table.ClassName));
            }
            sb.AppendLine();*/
            sb.AppendLine("            foreach (Table table in m_DbSchema.Tables)");
            sb.AppendLine("            {");
            sb.AppendLine("                foreach (TableColumn column in table.Columns)");
            sb.AppendLine("                {");
            sb.AppendLine("                    if (column.FKID != null && column.FKTable == null)");
            sb.AppendLine("                    {");
            sb.AppendLine("                        foreach (Table fkTable in m_DbSchema.Tables)");
            sb.AppendLine("                        {");
            sb.AppendLine("                            if (column.FKTableName == fkTable.TableName &&");
            sb.AppendLine("                                column.FKTableSchema == fkTable.Schema)");
            sb.AppendLine("                            {");
            sb.AppendLine("                                column.FKTable = fkTable;");
            sb.AppendLine("                            }");
            sb.AppendLine("                        }");
            sb.AppendLine("                    }");
            sb.AppendLine("                }");
            sb.AppendLine("            }");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public DbConnection GetThreadConnection()");
            sb.AppendLine("        {");
            sb.AppendLine("            return GetThreadConnectionStatic();");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        private static " + sqlConnectionClass + " GetThreadConnectionStatic()");
            sb.AppendLine("        {");
            sb.AppendLine("            lock (m_ThreadConnectionLocker)");
            sb.AppendLine("            {");
            sb.AppendLine("                " + sqlConnectionClass + " connection;");
            sb.AppendLine("                if (!m_ThreadConnection.TryGetValue(Thread.CurrentThread, out connection))");
            sb.AppendLine("                {");
            sb.AppendLine("                    connection = new " + sqlConnectionClass + "(m_ConnectionString);");
            sb.AppendLine("                    connection.Open();");
            sb.AppendLine("                    m_ThreadConnection.Add(Thread.CurrentThread, connection);");
            sb.AppendLine("                }");
            sb.AppendLine();
            sb.AppendLine("                return connection;");
            sb.AppendLine("            }");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public static string GetConnectionString()");
            sb.AppendLine("        {");
            sb.AppendLine("            return m_ConnectionString;");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public static Database GetSchema()");
            sb.AppendLine("        {");
            sb.AppendLine("            return m_DbSchema;");
            sb.AppendLine("        }");
            sb.AppendLine();
            foreach (Table table in db.Tables)
            {
                sb.AppendLine(string.Format("        public static TableQueryProvider<{0}> {0}", table.ClassName));
                sb.AppendLine("        {");
                sb.AppendLine(string.Format("            get {{ return new TableQueryProvider<{0}>(); }}", table.ClassName));
                //sb.AppendLine(string.Format("            get {{ if (m_{0} == null) {{ m_{0} = new TableQueryProvider<{0}>(); }} return m_{0}; }}", table.ClassName));
                sb.AppendLine("        }");
                sb.AppendLine();
            }
            sb.AppendLine("        string IDatabase.ConnectionString");
            sb.AppendLine("        {");
            sb.AppendLine("            get { return m_ConnectionString; }");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        Database IDatabase.Schema");
            sb.AppendLine("        {");
            sb.AppendLine("            get { return m_DbSchema; }");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        Type IDatabase.GetTableType(Table table)");
            sb.AppendLine("        {");
            sb.AppendLine("            return m_TableTypeDictionary[table.FullClassName];");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        int IDatabase.ExecuteSql(string sql, DbParameter[] parameters)");
            sb.AppendLine("        {");
            sb.AppendLine("            return ExecuteSql(sql, parameters == null ? null : parameters.Cast<" + sqlParameterClass + ">().ToArray());");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        int IDatabase.ExecuteIdentityInsert(string sql, DbParameter[] parameters)");
            sb.AppendLine("        {");
            sb.AppendLine("            Guard.ArgumentNotNullOrEmptyString(sql, \"sql\");");
            sb.AppendLine();
            sb.AppendLine("            DataRow dataRow = " + dataHelperClass + ".GetDataRow(GetThreadConnectionStatic(), sql, parameters == null ? null : parameters.Cast<" + sqlParameterClass + ">().ToArray());");
            sb.AppendLine("            return Convert.ToInt32(dataRow[0]);");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        private static int ExecuteSql(string sql, DbParameter[] parameters)");
            sb.AppendLine("        {");
            sb.AppendLine("            Guard.ArgumentNotNullOrEmptyString(sql, \"sql\");");
            sb.AppendLine();
            sb.AppendLine("            return " + dataHelperClass + ".ExecuteSQL(GetThreadConnectionStatic(), sql, parameters == null ? null : parameters.Cast<" + sqlParameterClass + ">().ToArray());");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine(string.Format("        public static void Delete<T>(T item)"));
            sb.AppendLine(string.Format("            where T : BusinessObjectLinq<{0}>", dbClassName));
            sb.AppendLine("        {");
            sb.AppendLine("            if (item == null)");
            sb.AppendLine("                throw new ArgumentNullException(\"item\", \"Parameter 'item' cannot be null.\");");
            sb.AppendLine();
            sb.AppendLine("            Delete<T>(new[] { item });");
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine(string.Format("        public static void Delete<T>(IEnumerable<T> items)"));
            sb.AppendLine(string.Format("            where T : BusinessObjectLinq<{0}>", dbClassName));
            sb.AppendLine("        {");
            sb.AppendLine("            if (items == null)");
            sb.AppendLine("                throw new ArgumentNullException(\"items\", \"Parameter 'items' cannot be null.\");");
            sb.AppendLine();
            sb.AppendLine("            List<T> list = items.ToList();");
            sb.AppendLine("            if (list.Count == 0)");
            sb.AppendLine("                throw new ArgumentException(\"Parameter 'items' cannot be empty.\", \"items\");");
            sb.AppendLine();
            sb.AppendLine(string.Format("            Table table = BusinessObjectLinq<{0}>.GetTable(typeof(T));", dbClassName));
            sb.AppendLine("            bool useSchema = !string.IsNullOrEmpty(table.Schema);");
            sb.AppendLine();
            sb.AppendLine("            List<string> deleteStatementList = new List<string>();");
            sb.AppendLine("            StringBuilder deleteStatement = new StringBuilder();");
            sb.AppendLine("            for (int i = 0; i < list.Count; i++)");
            sb.AppendLine("            {");
            sb.AppendLine(string.Format("                BusinessObjectLinq<{0}> item = list[i];", dbClassName));
            sb.AppendLine("                if (item == null)");
            sb.AppendLine("                    throw new InvalidOperationException(string.Format(\"Item at index {0} is null.\", i));");
            sb.AppendLine();
            sb.AppendLine("                if (deleteStatement.Length == 0)");
            sb.AppendLine("                {");
            sb.AppendLine("                    if (useSchema)");
            sb.AppendLine("                        deleteStatement.Append(string.Format(\"DELETE FROM [{0}].[{1}] WHERE \", table.Schema, table.TableName));");
            sb.AppendLine("                    else");
            sb.AppendLine("                        deleteStatement.Append(string.Format(\"DELETE FROM [{0}] WHERE \", table.TableName));");
            sb.AppendLine("                }");
            sb.AppendLine("                else");
            sb.AppendLine("                {");
            sb.AppendLine("                    deleteStatement.Append(\" OR \");");
            sb.AppendLine("                }");
            sb.AppendLine();
            sb.AppendLine(string.Format("                deleteStatement.Append(string.Format(\" ({{0}}) \", BusinessObjectLinq<{0}>.GetDeleteCriteria(item)));", dbClassName));
            sb.AppendLine();
            sb.AppendLine("                // Accomodate for overflow");
            sb.AppendLine("                if (deleteStatement.Length > 60000)");
            sb.AppendLine("                {");
            sb.AppendLine("                    deleteStatementList.Add(deleteStatement.ToString());");
            sb.AppendLine("                    deleteStatement = new StringBuilder();");
            sb.AppendLine("                }");
            sb.AppendLine("            }");
            sb.AppendLine();
            sb.AppendLine("            if (deleteStatement.Length != 0)");
            sb.AppendLine("            {");
            sb.AppendLine("                deleteStatementList.Add(deleteStatement.ToString());");
            sb.AppendLine("            }");
            sb.AppendLine();
            sb.AppendLine("            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))");
            sb.AppendLine("            {");
            sb.AppendLine("                foreach (string deleteSql in deleteStatementList)");
            sb.AppendLine("                {");
            sb.AppendLine("                    ExecuteSql(deleteSql, null);");
            sb.AppendLine("                }");
            sb.AppendLine();
            sb.AppendLine("                scope.Complete();");
            sb.AppendLine("            }");
            sb.AppendLine("        }");

            sb.AppendLine("    }");
            sb.AppendLine("}");

            File.WriteAllText(dbClassPath, sb.ToString());
            CleanUpCSharp(dbClassPath);

            return new[] { dbClassPath };
        }

        private static void AppendAutoGeneratedComment(StringBuilder sb)
        {
            sb.AppendLine("//------------------------------------------------------------------------------");
            sb.AppendLine("// <auto-generated>");
            sb.AppendLine(string.Format("//     This code was generated by LINQ Business Object Builder on {0:yyyy-MM-dd hh:mm:ss}.", DateTime.Now));
            sb.AppendLine(string.Format("//     Version: {0}", Assembly.GetExecutingAssembly().GetName().Version));
            sb.AppendLine("//");
            sb.AppendLine("//     Changes to this file may cause incorrect behavior and will be lost if");
            sb.AppendLine("//     the code is regenerated.");
            sb.AppendLine("// </auto-generated>");
            sb.AppendLine("//------------------------------------------------------------------------------");
            sb.AppendLine();
        }

        private static string GetDbClassName(Database db)
        {
            Guard.ArgumentNotNull(db, "db");

            return db.DatabaseName + "Db";
        }

        private static string[] CreateClass(string projectOutputDirectory, string baseNamespace, Database db, Table table)
        {
            Guard.ArgumentNotNullOrEmptyString(projectOutputDirectory, "projectOutputDirectory");
            Guard.ArgumentNotNullOrEmptyString(baseNamespace, "baseNamespace");
            Guard.ArgumentNotNull(db, "db");
            Guard.ArgumentNotNull(table, "table");

            string dbClassName = GetDbClassName(db);
            string setReadOnlyPropertyBlock = string.Empty;
            string setMaxLengthPropertyBlock = string.Empty;
            string setRequiredBlock = string.Empty;
            string propertiesBlock = string.Empty;
            string declarationBlock = string.Empty;
            string declarationBlock2 = string.Empty;
            string assignmentBlock = string.Empty;

            string internalInterfaceName = string.Format("I{0}Internals", table.ClassName);
            string publicDeclarationBlock2 = string.Empty;
            string publicPropertiesBlock = string.Empty;
            string publicDeclarationBlock2Implementation = string.Empty;
            string publicPropertiesBlockImplementation = string.Empty;

            // Table columns
            foreach (TableProperty property in table.Properties)
            {
                string propertyName = property.PropertyName;
                string dataType = GetFriendlyDataType(db.Tables, property);

                TableProperty linkedProperty = null;
                string linkedPropertyAccess = null;
                bool isLinkedProperty = false;
                bool isNullable = true;
                if (property.Column != null)
                {
                    isNullable = property.Column.IsNullable;
                    linkedProperty = table.Properties.Where(p => p.Column == property.Column && p != property && !p.IsPrimitiveType).SingleOrDefault();
                    if (linkedProperty != null)
                    {
                        Table fkTable = db.Tables.Where(p => p.TableName == property.Column.FKTableName && p.Schema == property.Column.FKTableSchema).Single();
                        List<TableProperty> fkPKPropertyList = fkTable.Properties.Where(p => p.Column != null && p.Column.ColumnName == property.Column.FKTableColumnName).ToList();
                        TableProperty fkPKProperty;
                        if (fkPKPropertyList.Count == 2)
                        {
                            // TODO: is this the right way to pick the linked property?
                            fkPKProperty = fkPKPropertyList.Where(p => p.IsPrimitiveType == property.IsPrimitiveType).Single();
                        }
                        else if (fkPKPropertyList.Count != 1)
                        {
                            throw new Exception(string.Format("don't know what to do with {0} properties", fkPKPropertyList.Count));
                        }
                        else
                        {
                            fkPKProperty = fkPKPropertyList.Single();
                        }
                        linkedPropertyAccess = string.Format("{0}.{1}", linkedProperty.PropertyName, fkPKProperty.PropertyName);
                        isLinkedProperty = true;
                    }
                }

                bool isBindingList = property.IsChildList;

                string declaration;
                if (!isLinkedProperty)
                {
                    if (!isBindingList)
                        declaration = string.Format("\t\tprivate Property<{0}> m_{1};", dataType, propertyName);
                    else
                        declaration = string.Format("\t\tprivate ChildListProperty<{0}, {2}> m_{1};", dataType, propertyName, property.ChildDataType);
                    declarationBlock += declaration + Environment.NewLine;
                }

                if (!isBindingList)
                {
                    declaration = string.Format("\t\tprotected bool m_Is{0}LoadedFromDb = false;", propertyName);
                    declarationBlock2 += declaration + Environment.NewLine;

                    declaration = string.Format("\t\tprotected {0} m_{1}FromDb = default({0});", (dataType == "bool?" && !isNullable ? "bool" : dataType), propertyName);
                    declarationBlock2 += declaration + Environment.NewLine;

                    // Public interface
                    declaration = string.Format("\t\tbool m_Is{0}LoadedFromDb {{ get; set; }}", propertyName);
                    publicDeclarationBlock2 += declaration + Environment.NewLine;

                    declaration = string.Format("\t\t{0} m_{1}FromDb {{ get; set; }}", (dataType == "bool?" && !isNullable ? "bool" : dataType), propertyName);
                    publicDeclarationBlock2 += declaration + Environment.NewLine;

                    publicDeclarationBlock2Implementation += string.Format("\t\tbool {1}.m_Is{0}LoadedFromDb {{ get {{ return m_Is{0}LoadedFromDb; }} set {{ m_Is{0}LoadedFromDb = value; }} }}", propertyName, internalInterfaceName) + Environment.NewLine;
                    publicDeclarationBlock2Implementation += string.Format("\t\t{0} {2}.m_{1}FromDb {{ get {{ return m_{1}FromDb; }} set {{ m_{1}FromDb = value; }} }}", (dataType == "bool?" && !isNullable ? "bool" : dataType), propertyName, internalInterfaceName) + Environment.NewLine;
                }

                StringBuilder propertyBody = new StringBuilder();
                propertyBody.AppendLine(string.Format("        public {0} {1}", dataType, propertyName));
                propertyBody.AppendLine("        {");
                // Public interface
                publicPropertiesBlock += string.Format("        {0} {1} {{ get; ", dataType, propertyName);
                publicPropertiesBlockImplementation += string.Format("        {0} {2}.{1} {{ get {{ return {1}; }} ", dataType, propertyName, internalInterfaceName);
                if (!isBindingList)
                {
                    propertyBody.AppendLine(string.Format("            get"));
                    propertyBody.AppendLine(string.Format("            {{"));
                    propertyBody.AppendLine(string.Format("                if (!PropertiesInitialized)"));
                    propertyBody.AppendLine(string.Format("                {{"));
                    propertyBody.AppendLine(string.Format("                    if (m_Is{0}LoadedFromDb)", propertyName));
                    propertyBody.AppendLine(string.Format("                        return m_{0}FromDb;", propertyName));
                    propertyBody.AppendLine(string.Format("                    else"));
                    propertyBody.AppendLine(string.Format("                        Refresh();"));
                    propertyBody.AppendLine(string.Format("                }}"));
                    if (!isLinkedProperty)
                        propertyBody.AppendLine(string.Format("                return m_{0}.Value;", propertyName));
                    else
                        propertyBody.AppendLine(string.Format("                return ({0} == null ? null : {1});", linkedProperty.PropertyName, linkedPropertyAccess));
                    propertyBody.AppendLine(string.Format("            }}"));
                }
                else
                {
                    /*//string getChildWhere = string.Empty;

                    // ReSharper disable AccessToModifiedClosure
                    Table childTable = db.Tables.Where(p => p.FullClassName == property.ChildDataType).Single();
                    List<TableProperty> myPKs_old = table.Properties.Where(p => table.PrimaryKey.Contains(p.Column)).ToList();
                    List<TableProperty> myPKs = table.Properties.Where(p => table.UniqueColumns.Contains(p.Column)).ToList();
                    if (myPKs.Count != myPKs_old.Count)
                    {
                    }
                    IEnumerable<IGrouping<int?, TableProperty>> enumGroupChildFKs = childTable.Properties.Where(p => p.Column != null && p.IsPrimitiveType && p.Column.FKTableSchema == table.Schema && p.Column.FKTableName == table.TableName).GroupBy(p => p.Column.FKID);
                    // ReSharper restore AccessToModifiedClosure

                    foreach (IGrouping<int?, TableProperty> groupChildFKs in enumGroupChildFKs)
                    {
                        List<TableProperty> childFKs = groupChildFKs.ToList();

                        Debug.Assert(myPKs.Count == childFKs.Count, "myPKs.Count == childFKs.Count");

                        foreach (TableProperty pk in myPKs)
                        {
                            // ReSharper disable AccessToModifiedClosure
                            TableProperty fk = childFKs.Where(p => p.Column.FKTableColumnName == pk.Column.ColumnName).SingleOrDefault();
                            if (fk == null)
                            {
                                string msg = string.Format("FK in table '{0}.{1}' which references '{2}' in '{3}.{4}' cannot be found.", childTable.Schema, childTable.TableName, pk.Column.ColumnName, table.Schema, table.TableName);
                                throw new Exception(msg);
                            }
                            // ReSharper restore AccessToModifiedClosure

                            Debug.Assert(pk.DataType == fk.DataType, "pk.DataType == fk.DataType");

                            //if (!string.IsNullOrEmpty(getChildWhere))
                            //    getChildWhere += " && ";

                            //getChildWhere += string.Format("p.{0} == {1}", fk.PropertyName, pk.PropertyName);
                        }
                    }*/

                    //Debug.Assert(!string.IsNullOrEmpty(getChildWhere), "!string.IsNullOrEmpty(getChildWhere)");

                    propertyBody.AppendLine("            get");
                    propertyBody.AppendLine("            {");
                    propertyBody.AppendLine("                CheckInitialized();");
                    propertyBody.AppendLine("                return m_" + propertyName + ".Value;");
                    propertyBody.AppendLine("            }");

                    /*propertyBody.AppendLine("            get");
                    propertyBody.AppendLine("            {");
                    propertyBody.AppendLine("                if (!m_Is" + propertyName + "BindingListInitialized)");
                    propertyBody.AppendLine("                {");
                    propertyBody.AppendLine("                    lock (m_Is" + propertyName + "BindingListInitializedLocker)");
                    propertyBody.AppendLine("                    {");
                    propertyBody.AppendLine("                        if (!m_Is" + propertyName + "BindingListInitialized)");
                    propertyBody.AppendLine("                        {");
                    propertyBody.AppendLine("                            CheckInitialized();");
                    propertyBody.AppendLine();
                    propertyBody.AppendLine("                            " + dataType + " list;");
                    propertyBody.AppendLine("                            if (IsNew)");
                    propertyBody.AppendLine("                                list = new " + dataType + "();");
                    propertyBody.AppendLine("                            else");
                    propertyBody.AppendLine("                                list = new " + dataType + "(" + dbClassName + "." + propertyName + ".Where(p => " + getChildWhere + ").ToList());");
                    propertyBody.AppendLine();
                    propertyBody.AppendLine("                            AddDetailList(\"" + propertyName + "\", list);");
                    propertyBody.AppendLine("                            m_Is" + propertyName + "BindingListInitialized = true;");
                    propertyBody.AppendLine("                            m_" + propertyName + ".Value = list;");
                    propertyBody.AppendLine("                        }");
                    propertyBody.AppendLine("                    }");
                    propertyBody.AppendLine("                }");
                    propertyBody.AppendLine();
                    propertyBody.AppendLine("                return m_" + propertyName + ".Value;");
                    propertyBody.AppendLine("            }");*/
                }

                if (!isBindingList && !isLinkedProperty)
                {
                    if (property.Column != null && !property.Column.IsReadOnly)
                    {
                        propertyBody.AppendLine("            set");
                        propertyBody.AppendLine("            {");
                        propertyBody.AppendLine("                CheckInitialized();");
                        propertyBody.AppendLine("                m_" + propertyName + ".Value = value;");
                        propertyBody.AppendLine("            }");

                        // Public interface
                        publicPropertiesBlock += "set; ";
                        publicPropertiesBlockImplementation += string.Format("set {{ {0} = value; }}", propertyName);
                    }
                    else if (!property.IsChildList)
                    {
                        propertyBody.AppendLine("            protected set");
                        propertyBody.AppendLine("            {");
                        propertyBody.AppendLine("                CheckInitialized();");
                        propertyBody.AppendLine("                m_" + propertyName + ".Value = value;");
                        propertyBody.AppendLine("            }");

                        // Public interface
                        publicPropertiesBlock += "set; ";
                        publicPropertiesBlockImplementation += string.Format("set {{ {0} = value; }}", propertyName);
                    }
                }

                propertyBody.AppendLine("        }");
                propertiesBlock += propertyBody + Environment.NewLine;

                // Public interface
                publicPropertiesBlock += "}" + Environment.NewLine;
                publicPropertiesBlockImplementation += "}" + Environment.NewLine;

                string assignment = string.Empty;
                if (!isBindingList && !isLinkedProperty)
                {
                    assignment = string.Format("\t\t\t\t\t\titem.m_{0} = new Property<{1}>(item, \"{0}\", item.m_{0}FromDb, {2});", propertyName, dataType, property.IsChildContainer.ToString().ToLower());
                }
                else if (isBindingList)
                {
                    string getChildWhere = string.Empty;

                    // ReSharper disable AccessToModifiedClosure
                    Table childTable = db.Tables.Where(p => p.FullClassName == property.ChildDataType).Single();
                    List<TableProperty> myPKs = table.Properties.Where(p => p.Column != null && p.IsPrimitiveType == true).ToList(); // trust FK contraints (may references unique index, not just PK or UQ constraint)
                    //List<TableProperty> myPKs = table.Properties.Where(p => table.PrimaryKey.Contains(p.Column)).ToList();
                    IEnumerable<IGrouping<int?, TableProperty>> enumGroupChildFKs = childTable.Properties.Where(p => p.Column != null && p.IsPrimitiveType && p.Column.FKTableSchema == table.Schema && p.Column.FKTableName == table.TableName).GroupBy(p => p.Column.FKID);
                    List<IGrouping<int?, TableProperty>> enumChildFKADT = childTable.Properties.Where(p => p.Column != null && !p.IsPrimitiveType && p.Column.FKTableSchema == table.Schema && p.Column.FKTableName == table.TableName).GroupBy(p => p.Column.FKID).ToList();
                    // ReSharper restore AccessToModifiedClosure

                    foreach (IGrouping<int?, TableProperty> groupChildFKs in enumGroupChildFKs)
                    {
                        List<TableProperty> childFKs = groupChildFKs.ToList();

                        if (!childFKs.Where(p => p.Column.ColumnName == property.ChildColumnName).Any())
                            continue;

                        TableProperty childFKADT = enumChildFKADT.Where(p => p.Key == groupChildFKs.Key).Single().ToList().Single(); // looks odd, breaks grouping into results
                        //TableProperty childFKADT = childTable.Properties.Where(p => p.Column != null && !p.IsPrimitiveType && p.Column.FKTableSchema == table.Schema && p.Column.FKTableName == table.TableName).Single();

                        //Debug.Assert(myPKs.Count == childFKs.Count, "myPKs.Count == childFKs.Count");

                        foreach (TableProperty fk in childFKs)
                        {
                            // ReSharper disable AccessToModifiedClosure
                            //TableProperty fk = childFKs.Where(p => p.Column.FKTableColumnName == pk.Column.ColumnName).Single();
                            TableProperty pk = myPKs.Where(p => fk.Column.FKTableColumnName == p.Column.ColumnName).Single();
                            // ReSharper restore AccessToModifiedClosure

                            if (!string.IsNullOrEmpty(getChildWhere))
                                getChildWhere += " && ";

                            getChildWhere += string.Format("p.{0} == item.{1}", fk.PropertyName, pk.PropertyName);
                        }

                        /*foreach (TableProperty pk in myPKs)
                        {
                            // ReSharper disable AccessToModifiedClosure
                            TableProperty fk = childFKs.Where(p => p.Column.FKTableColumnName == pk.Column.ColumnName).Single();
                            // ReSharper restore AccessToModifiedClosure

                            if (!string.IsNullOrEmpty(getChildWhere))
                                getChildWhere += " && ";

                            getChildWhere += string.Format("p.{0} == item.{1}", fk.PropertyName, pk.PropertyName);
                        }*/

                        Debug.Assert(!string.IsNullOrEmpty(getChildWhere), "!string.IsNullOrEmpty(getChildWhere)");
                        string misctext = "() => new " + dataType + "(" + dbClassName + "." + childTable.ClassName + ".Where(p => " + getChildWhere + ").ToList())";
                        assignment = string.Format("\t\t\t\t\t\titem.m_{0} = new ChildListProperty<{1}, {3}>(item, \"{0}\", p => p.{4} = item, {2}, {5}.Delete<{3}>);", propertyName, dataType, misctext, property.ChildDataType, childFKADT.PropertyName, dbClassName);
                    }
                }

                if (!string.IsNullOrEmpty(assignment))
                    assignmentBlock += assignment + Environment.NewLine;

                if (property.Column != null && !isLinkedProperty)
                {
                    if (property.Column.IsReadOnly)
                    {
                        setReadOnlyPropertyBlock += string.Format("\t\t\t\t\t\titem.m_{0}.IsReadOnly = true;{1}", propertyName, Environment.NewLine);
                    }

                    if (property.Column.CharacterMaxLength != null)
                    {
                        setMaxLengthPropertyBlock += string.Format("\t\t\t\t\t\titem.m_{0}.MaxLength = {1};{2}", propertyName, property.Column.CharacterMaxLength, Environment.NewLine);
                    }

                    if (!property.Column.IsNullable && !property.Column.IsReadOnly)
                    {
                        setRequiredBlock += string.Format("\t\t\t\t\t\titem.AddRequiredField(item.m_{0}, \"{2} is required.\");{1}", propertyName, Environment.NewLine, StringConverter.GetPrettyName(propertyName));
                    }
                }
            }

            string setUniqueBlock = string.Empty;
            foreach (UniqueConstraint uq in table.UniqueConstraints)
            {
                string uqProps = string.Empty;
                foreach (TableColumn uqColumn in uq.Columns)
                {
                    TableProperty[] uqProperties =
                        table.Properties.Where(p => p.Column != null && p.Column.ColumnName == uqColumn.ColumnName).ToArray();

                    TableProperty uqProperty = uqProperties.Length == 1 ? uqProperties[0] : uqProperties.Where(p => !p.IsPrimitiveType).Single();
                    if (uqProps.Length > 0)
                        uqProps += ", ";
                    uqProps += string.Format("item.m_{0}", uqProperty.PropertyName);
                }

                setUniqueBlock += string.Format("\t\t\t\t\t\titem.AddUniqueField(new ComplexProperty[] {{ {0} }});{1}", uqProps, Environment.NewLine);
            }

            // Generate code            

            StringBuilder sb = new StringBuilder();
            AppendAutoGeneratedComment(sb);
            sb.AppendLine("using System;");
            sb.AppendLine("using System.ComponentModel;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using CSW.Framework.Common.Binding;");
            sb.AppendLine("using CSW.Framework.Linq;");
            sb.AppendLine();
            sb.AppendLine(string.Format("namespace {0}", baseNamespace));
            sb.AppendLine("{");
            sb.AppendLine(string.Format("\tpublic partial class {0} : BusinessObjectLinq<{1}>, {2}", table.ClassName, GetDbClassName(db), internalInterfaceName));
            sb.AppendLine("\t{");
            sb.AppendLine(declarationBlock);
            sb.AppendLine();
            sb.AppendLine("#pragma warning disable 169 // disable 'field is never used' warning");
            sb.Append(declarationBlock2);
            sb.AppendLine("#pragma warning restore 169");
            sb.AppendLine();
            //sb.AppendLine("\t\tprivate bool m_IsInitialized;");
            //sb.AppendLine("\t\tprivate readonly object m_IsInitializedLocker = new object();");
            sb.AppendLine();
            sb.AppendLine(string.Format("\t\tpublic {0}()", table.ClassName));
            sb.AppendLine("\t\t{");
            sb.AppendLine("\t\t}");
            sb.AppendLine();
            sb.AppendLine(string.Format("\t\tprivate static void CreateProperties({0} item)", table.ClassName));
            sb.AppendLine("\t\t{");
            sb.AppendLine("\t\t\tif (!item.PropertiesInitialized)");
            sb.AppendLine("\t\t\t{");
            sb.AppendLine("\t\t\t\tlock (item.PropertiesInitializedLocker)");
            sb.AppendLine("\t\t\t\t{");
            sb.AppendLine("\t\t\t\t\tif (!item.PropertiesInitialized)");
            sb.AppendLine("\t\t\t\t\t{");
            sb.AppendLine(assignmentBlock);
            sb.AppendLine();
            sb.AppendLine("\t\t\t\t\t\titem.PropertiesInitialized = true;");
            sb.AppendLine();
            sb.AppendLine(setReadOnlyPropertyBlock);
            sb.AppendLine(setMaxLengthPropertyBlock);
            sb.AppendLine(setRequiredBlock);
            sb.AppendLine(setUniqueBlock);
            sb.AppendLine();
            sb.AppendLine("\t\t\t\t\t\titem.CreateRules();");
            sb.AppendLine("\t\t\t\t\t}");
            sb.AppendLine("\t\t\t\t}");
            sb.AppendLine("\t\t\t}");
            sb.AppendLine("\t\t}");
            sb.AppendLine();
            sb.AppendLine("        public override bool HasComputedColumns");
            sb.AppendLine("        {");
            sb.AppendLine(string.Format("            get {{ return {0}; }}", table.HasComputedColumns ? "true" : "false"));
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("        public override void Delete()");
            sb.AppendLine("        {");
            sb.AppendLine(string.Format("            {0}.Delete(this);", dbClassName));
            sb.AppendLine("        }");
            sb.AppendLine();
            sb.AppendLine("\t\tprotected override void CheckInitialized()");
            sb.AppendLine("\t\t{");
            sb.AppendLine("\t\t\tif (!PropertiesInitialized)");
            sb.AppendLine("\t\t\t{");
            sb.AppendLine("\t\t\t\tlock (PropertiesInitializedLocker)");
            sb.AppendLine("\t\t\t\t{");
            sb.AppendLine("\t\t\t\t\tif (!PropertiesInitialized)");
            sb.AppendLine("\t\t\t\t\t{");
            sb.AppendLine("\t\t\t\t\t\tbase.CheckInitialized();");
            sb.AppendLine("\t\t\t\t\t\tCreateProperties(this);");
            sb.AppendLine("\t\t\t\t\t}");
            sb.AppendLine("\t\t\t\t}");
            sb.AppendLine("\t\t\t}");
            sb.AppendLine("\t\t}");
            sb.AppendLine();
            sb.AppendLine("\t\tpartial void CreateRules();");
            sb.AppendLine();
            sb.AppendLine(propertiesBlock);
            sb.AppendLine();
            sb.AppendLine(publicDeclarationBlock2Implementation);
            sb.AppendLine();
            sb.AppendLine(publicPropertiesBlockImplementation);
            sb.AppendLine();
            sb.AppendLine("\t}");
            sb.AppendLine("}");

            string generatedTableOutputPath = Path.Combine(projectOutputDirectory, @"Tables\Generated");
            if (!Directory.Exists(generatedTableOutputPath))
                Directory.CreateDirectory(generatedTableOutputPath);
            generatedTableOutputPath = Path.Combine(generatedTableOutputPath, string.Format("{0}.Generated.cs", table.ClassName));
            File.WriteAllText(generatedTableOutputPath, sb.ToString());
            CleanUpCSharp(generatedTableOutputPath);

            sb = new StringBuilder();
            AppendAutoGeneratedComment(sb);
            sb.AppendLine("using System;");
            sb.AppendLine("using System.ComponentModel;");
            sb.AppendLine("using CSW.Framework.Common.Binding;");
            sb.AppendLine("using CSW.Framework.Linq;");
            sb.AppendLine();
            sb.AppendLine(string.Format("namespace {0}", baseNamespace));
            sb.AppendLine("{");
            sb.AppendLine(string.Format("\tpublic interface {0} : IBusinessObjectLinqPublic", internalInterfaceName));
            sb.AppendLine("\t{");
            sb.AppendLine(publicDeclarationBlock2);
            sb.AppendLine();
            sb.AppendLine(publicPropertiesBlock);
            sb.AppendLine("\t}");
            sb.AppendLine("}");

            string interfaceOutputPath = Path.Combine(projectOutputDirectory, @"Tables\InternalInterfaces");
            if (!Directory.Exists(interfaceOutputPath))
                Directory.CreateDirectory(interfaceOutputPath);
            interfaceOutputPath = Path.Combine(interfaceOutputPath, string.Format("{0}.Generated.cs", internalInterfaceName));
            File.WriteAllText(interfaceOutputPath, sb.ToString());
            CleanUpCSharp(interfaceOutputPath);

            string tableOutputPath = Path.Combine(projectOutputDirectory, @"Tables");
            if (!Directory.Exists(tableOutputPath))
                Directory.CreateDirectory(tableOutputPath);
            tableOutputPath = Path.Combine(tableOutputPath, string.Format("{0}.cs", table.ClassName));
            if (!File.Exists(tableOutputPath))
            {
                string hashCodeGetter = string.Empty;

                if (table.PrimaryKey.Length == 1)
                {
                    TableColumn pkColumn = table.PrimaryKey[0];
                    TableProperty pkProperty = table.Properties.Where(p => p.Column == pkColumn && p.IsPrimitiveType).Single();

                    if (pkProperty.Type == typeof(int))
                    {
                        hashCodeGetter = pkProperty.PropertyName + " ?? base.GetHashCode()";
                    }
                }

                StringBuilder pkCompare = new StringBuilder();
                foreach (TableColumn pkColumn in table.PrimaryKey)
                {
                    if (pkCompare.Length != 0)
                        pkCompare.Append(" && ");

                    TableProperty pkProperty = table.Properties.Where(p => p.Column == pkColumn && p.IsPrimitiveType).Single();

                    pkCompare.Append(string.Format("{0} != null && {0} == item.{0}", pkProperty.PropertyName));
                }

                string singleVarcharPropertyName = null;
                foreach (TableColumn tableColumn in table.Columns)
                {
                    TableProperty property = table.Properties.Where(p => p.Column == tableColumn && p.IsPrimitiveType).Single();

                    if (property.Type == typeof(string) && tableColumn.FKID == null)
                    {
                        if (!string.IsNullOrEmpty(singleVarcharPropertyName))
                        {
                            singleVarcharPropertyName = null;
                            break;
                        }
                        else
                        {
                            singleVarcharPropertyName = property.PropertyName;
                        }
                    }
                }

                // User edittable code
                sb = new StringBuilder();
                sb.AppendLine("using System;");
                sb.AppendLine("using System.ComponentModel;");
                sb.AppendLine("using System.Linq;");
                sb.AppendLine("using CSW.Framework.Common;");
                sb.AppendLine("using CSW.Framework.Linq;");
                sb.AppendLine();
                sb.AppendLine(string.Format("namespace {0}", baseNamespace));
                sb.AppendLine("{");
                sb.AppendLine(string.Format("\tpartial class {0}", table.ClassName));
                sb.AppendLine("\t{");
                sb.AppendLine("\t\t//partial void CreateRules()");
                sb.AppendLine("\t\t//{");
                sb.AppendLine("\t\t\t/* Quick Tips");
                sb.AppendLine("\t\t\t   1. Subscribe to m_PropertyName.PropertyChanged to respond to a changed value.");
                sb.AppendLine("\t\t\t   2. Use AddCheckConstraint(m_PropertyName, private void CheckMethodName(ErrorMessage errorMessage));");
                sb.AppendLine("\t\t\t      to add validation logic. If validation fails call errorMessage.Append(string)");
                sb.AppendLine("\t\t\t      inside the check method.");
                sb.AppendLine("\t\t\t   3. If validation depends on another property add one (or many) [DependsOn(\"\")]");
                sb.AppendLine("\t\t\t      attribute(s) to the check method.");
                sb.AppendLine("\t\t\t   4. Set m_PropertyName.IsReadOnly to toggle read only status from the");
                sb.AppendLine("\t\t\t      user's perspective.");
                sb.AppendLine("\t\t\t   5. To override a read only property use m_PropertyName.OverrideReadOnlyValue.");
                sb.AppendLine("\t\t\t   6. Use AddRequiredField to add a check constraint for a required field.");
                sb.AppendLine("\t\t\t      Once AddRequiredField is called, you can turn on/off the requirement by using");
                sb.AppendLine("\t\t\t      m_Property.IsRequired; however, setting IsRequired without first using");
                sb.AppendLine("\t\t\t      AddRequiredField has no effect. Fields that are NOT NULL in the database already");
                sb.AppendLine("\t\t\t      use AddRequiredField in the TableName.Generated.cs file. */");
                sb.AppendLine("\t\t//}");
                sb.AppendLine();
                sb.AppendLine("        #region Override ToString, Equals, GetHashCode");
                sb.AppendLine();
                sb.AppendLine("        /// <summary>");
                sb.AppendLine(string.Format("        /// Returns a <see cref=\"T:System.String\"/> that represents the current <see cref=\"{0}\"/>.", table.ClassName));
                sb.AppendLine("        /// </summary>");
                sb.AppendLine(string.Format("        /// <returns>A <see cref=\"T:System.String\"/> that represents the current <see cref=\"{0}\"/>.</returns>", table.ClassName));
                sb.AppendLine("        public override string ToString()");
                sb.AppendLine("        {");
                if (!string.IsNullOrEmpty(singleVarcharPropertyName))
                    sb.AppendLine(string.Format("            return {0};", singleVarcharPropertyName));
                else
                    sb.AppendLine("            return \"Override className.ToString()\";");
                sb.AppendLine("        }");
                sb.AppendLine();
                sb.AppendLine("        /// <summary>");
                sb.AppendLine(string.Format("        /// Determines whether the specified <see cref=\"T:System.Object\"/> is equal to the current <see cref=\"{0}\"/>.", table.ClassName));
                sb.AppendLine("        /// </summary>");
                sb.AppendLine(string.Format("        /// <param name=\"obj\">The <see cref=\"T:System.Object\"/> to compare with the current <see cref=\"{0}\"/>.</param>", table.ClassName));
                sb.AppendLine(string.Format("        /// <returns>true if the specified <see cref=\"T:System.Object\"/> is equal to the current <see cref=\"{0}\"/>; otherwise, false.</returns>", table.ClassName));
                sb.AppendLine("        public override bool Equals(object obj)");
                sb.AppendLine("        {");
                sb.AppendLine("            if (obj == this)");
                sb.AppendLine("                return true;");
                sb.AppendLine();
                sb.AppendLine("            if (obj == null)");
                sb.AppendLine("                return false;");
                sb.AppendLine();
                sb.AppendLine("            if (IsNew)");
                sb.AppendLine("                return false;");
                sb.AppendLine();
                sb.AppendLine(string.Format("            {0} item = obj as {0};", table.ClassName));
                sb.AppendLine("            if (item == null)");
                sb.AppendLine("                return false;");
                sb.AppendLine(string.Format("            if ({0})", pkCompare));
                sb.AppendLine("                return true;");
                sb.AppendLine();
                sb.AppendLine("            return false;");
                sb.AppendLine("        }");
                sb.AppendLine();
                sb.AppendLine("        /// <summary>");
                sb.AppendLine("        /// Serves as a hash function for a particular type.");
                sb.AppendLine("        /// </summary>");
                sb.AppendLine(string.Format("        /// <returns>A hash code for the current <see cref=\"{0}\"/>.</returns>", table.ClassName));
                sb.AppendLine("        public override int GetHashCode()");
                sb.AppendLine("        {");
                if (!string.IsNullOrEmpty(hashCodeGetter))
                    sb.AppendLine(string.Format("            return {0};", hashCodeGetter));
                else
                    sb.AppendLine("            return base.GetHashCode();");
                sb.AppendLine("        }");
                sb.AppendLine();
                sb.AppendLine("        #endregion");
                sb.AppendLine("\t}");
                sb.AppendLine("}");

                File.WriteAllText(tableOutputPath, sb.ToString());
                CleanUpCSharp(tableOutputPath);
            }

            return new[] { tableOutputPath, generatedTableOutputPath, interfaceOutputPath };
        }

        /*private static string GetFriendlyPrimitiveDataType(TableProperty property)
        {
            return GetFriendlyDataType(null, property, false);
        }*/

        private static string GetFriendlyDataType(IEnumerable<Table> tableList, TableProperty property)
        {
            return GetFriendlyDataType(tableList, property, true);
        }

        private static string GetFriendlyDataType(IEnumerable<Table> tableList, TableProperty property, bool getTypeAsADT)
        {
            Guard.ArgumentNotNull(property, "property");

            if (property.IsChildContainer)
            {
                return property.DataType;
            }
            else if (property.IsPrimitiveType || !getTypeAsADT)
            {
                string dataType = property.DataType;
                string friendlyTypeName;

                Type type = Type.GetType(dataType, true);
                if (type == typeof(string))
                    friendlyTypeName = "string";
                else if (type == typeof(int))
                    friendlyTypeName = "int";
                else if (type == typeof(short))
                    friendlyTypeName = "short";
                else if (type == typeof(double))
                    friendlyTypeName = "double";
                else if (type == typeof(float))
                    friendlyTypeName = "float";
                else if (type == typeof(DateTime))
                    friendlyTypeName = "DateTime";
                else if (type == typeof(decimal))
                    friendlyTypeName = "decimal";
                else if (type == typeof(bool))
                    friendlyTypeName = "bool";
                else
                    friendlyTypeName = type.FullName;

                if (!type.IsClass)
                    friendlyTypeName += "?";

                return friendlyTypeName;
            }
            else
            {
                Guard.ArgumentNotNull(tableList, "tableList");
                Guard.ArgumentNotNull(property.Column, "property.Column");

                return tableList.Where(p => p.Schema == property.Column.FKTableSchema && p.TableName == property.Column.FKTableName).Single().TableName;
            }
        }

        private const string m_GetSysColumnsStatement =
            @"select
                tbl.Name [TableName],
                col.Name [ColumnName],
                col.Is_Computed [IsComputed],
                sch.Name [Schema]
            from sys.objects tbl
            join sys.columns col
            on tbl.object_id = col.object_id
            join sys.schemas sch
            on sch.schema_id = tbl.schema_id
            where tbl.type = 'U'
            order by sch.name, tbl.name, col.column_id";

        private const string m_GetUniqueConstraintsStatementSqlCe =
            @"select 
                table_schema [Schema], 
                table_name [TableName], 
                column_name [ColumnName],
                index_name [ConstraintName]
             from information_schema.indexes 
             where primary_key = 0 and [unique] = 1";

        private const string m_GetUniqueConstraintsStatement =
            @"select 
                sch.name [Schema], 
                tbl.name [TableName], 
                col.name [ColumnName], 
                constr.name [ConstraintName]
            from sysobjects constr
            join sysobjects tbl
            on tbl.id = constr.parent_obj
            and constr.xtype = 'UQ'
            join sysindexes idx
            on constr.name = idx.name
            join sysindexkeys idxkey
            on idxkey.id = idx.id
            and idxkey.indid = idx.indid
            join syscolumns col
            on col.id = tbl.id
            and col.colid = idxkey.colid
            join sysusers sch
            on sch.uid = tbl.uid
            order by sch.name, tbl.name, constr.name, col.colid";

        private const string m_GetForeignKeysStatementSqlCe =
            @"select 
				kcu.CONSTRAINT_NAME,
                kcu.TABLE_SCHEMA [Schema], 
                kcu.TABLE_NAME [TableName], 
                kcu.COLUMN_NAME [ColumnName], 
                rc.UNIQUE_CONSTRAINT_SCHEMA [RefSchema], 
                rc.UNIQUE_CONSTRAINT_TABLE_NAME [RefTableName]
                from information_schema.key_column_usage kcu
                join information_schema.referential_constraints rc
                on rc.constraint_name = kcu.constraint_name";

        private const string m_GetForeignKeysStatement =
            @"select 
	            constid, 
	            keyno, 
	            uid1.name [Schema], 
	            so1.name [TableName], 
	            sc1.name [ColumnName], 
	            uid2.name [RefSchema], 
	            so2.name [RefTableName], 
	            sc2.name [RefColumnName]
            from sysforeignkeys fk
            join sysobjects so1
            on so1.id = fkeyid
            join sysobjects so2
            on so2.id = rkeyid
            join syscolumns sc1
            on sc1.id = so1.id
            and sc1.colid = fk.fkey
            join syscolumns sc2
            on sc2.id = so2.id
            and sc2.colid = fk.rkey
            join sysusers uid1
            on uid1.uid = so1.uid
            join sysusers uid2
            on uid2.uid = so2.uid
            order by uid1.name, so1.name, constid, keyno";

        private static string GetPrettyName(string input)
        {
            if (string.IsNullOrEmpty(input) || !char.IsUpper(input[0]))
                return input;

            // Split words based on casing
            List<string> words = new List<string>();
            int oldIndex = 0;
            for (int i = 1; i < input.Length; i++)
            {
                char c = input[i];

                if (char.IsUpper(c))
                {
                    string newWord = input.Substring(oldIndex, i - oldIndex);
                    words.Add(newWord);
                    oldIndex = i;
                }
            }

            words.Add(input.Substring(oldIndex));

            if (words.Count == 0)
                return input;

            // Aliases
            for (int i = 0; i < words.Count; i++)
            {
                switch (words[i])
                {
                    case "Qty":
                        words[i] = "Quantity";
                        break;
                    case "Desc":
                        words[i] = "Description";
                        break;
                    case "Cust":
                        words[i] = "Customer";
                        break;
                }
            }

            // Put words back together
            input = string.Empty;
            for (int i = 0; i < words.Count; i++)
            {
                string word = words[i];

                if (i == 0 && words.Count > 1 && word.EndsWith("_"))
                    continue;

                if (i > 0 && words[i - 1].Length == 1 && word.Length == 1)
                {
                    input += word;
                }
                else
                {
                    if (string.IsNullOrEmpty(input))
                        input = word;
                    else
                        input += " " + word;
                }
            }

            return input;
        }

        private static void CleanUpCSharp(string codeFile)
        {
            Guard.ArgumentNotNullOrEmptyString(codeFile, "codeFile");

            string[] lines = File.ReadAllLines(codeFile);
            StringBuilder newLines = new StringBuilder();

            bool lastLineIsBlank = false;
            bool lastLineIsOpenBrace = false;

            foreach (string line in lines)
            {
                bool lineIsBlank = false;
                bool lineIsCloseBrace = false;
                bool lineIsOpenBrace = false;

                string modLine = line.Replace("\t", "").Replace(" ", "");
                if (string.IsNullOrEmpty(modLine))
                {
                    lineIsBlank = true;
                }
                else
                {
                    if (modLine == "{")
                        lineIsOpenBrace = true;
                    else if (modLine == "}")
                        lineIsCloseBrace = true;
                }

                if (lineIsBlank && lastLineIsBlank)
                {
                    // don't output line
                }
                else if (lineIsCloseBrace && lastLineIsBlank)
                {
                    // remove last line
                    newLines.Remove(newLines.Length - 2, 2);
                    newLines.AppendLine(line.Replace("\t", "    ").TrimEnd(' '));
                }
                else if (lineIsBlank && lastLineIsOpenBrace)
                {
                    // don't output line

                    // set to previous line state
                    lineIsOpenBrace = true;
                    lineIsBlank = false;
                }
                else
                {
                    newLines.AppendLine(line.Replace("\t", "    ").TrimEnd(' '));
                }

                lastLineIsBlank = lineIsBlank;
                lastLineIsOpenBrace = lineIsOpenBrace;
            }

            try
            {
                File.WriteAllText(codeFile, newLines.ToString());
            }
            catch (IOException)
            {
                Thread.Sleep(100);
                File.WriteAllText(codeFile, newLines.ToString());
            }
        }
    }
}
